from pg import escape_bytea
from pg import unescape_bytea

from dbmodel import Species, Template


class DaoBase:
    def __init__(self, connection):
        self.connection = connection


class DaoSpecies(DaoBase):
    def get_species_list(self):
        queryResult = self.connection.query('select * from species')
        result = queryResult.getresult()
        fields = queryResult.listfields()
        species = []
        for row in result:
            species.append(Species(row[fields.index('id')], row[fields.index('name')], row[fields.index('comment')]))
        return species
    
    def get_species_by_id(self, id):
        queryResult = self.connection.query('select * from species where id = %s' % str(id))
        result = queryResult.getresult()
        fields = queryResult.listfields()
        row = result[0]
        return Species(row[fields.index('id')], row[fields.index('name')], fields.index('comment'))
    
    def insert_species(self, species):
        queryResult = self.connection.query('insert into species values (default, \'%s\', \'%s\') returning *' % (species.name, species.comment))
        result = queryResult.getresult()
        fields = queryResult.listfields()
        row = result[0]
        species.id = row[fields.index('id')]
        return species
    
    def update_species(self, species):
        self.connection.query('update species set name = \'%s\', comment = \'%s\' where id = %s' % (species.name, species.id, species.comment))
    
    def delete_species(self, species):
        self.connection.query('delete from species where id = %s' % species.id)
    

class DaoTemplate(DaoBase):
    def get_template_list(self):
        queryResult = self.connection.query('select id, id_species from template')
        result = queryResult.getresult()
        fields = queryResult.listfields()
        sounds = []
        for row in result:
            sounds.append(Template(row[fields.index('id')], row[fields.index('id_species')]))
        return sounds
    
    def get_template_by_id(self, template):
        queryResult = self.connection.query('select id, id_species from template where id = %s' % str(template.id))
        result = queryResult.getresult()
        fields = queryResult.listfields()
        row = result[0]
        return template(row[fields.index('id')], row[fields.index('id_species')])
    
    def get_templates_for_species(self, species):
        queryResult = self.connection.query('select id, id_species from template where id_species = %s' % str(species.id))
        result = queryResult.getresult()
        fields = queryResult.listfields()
        sounds = []
        for row in result:
            sounds.append(Template(row[fields.index('id')], row[fields.index('id_species')]))
        return sounds
    
    def load_sound_data_for_template(self, template):
        queryResult = self.connection.query('select data from template where id = %s' % str(template.id))
        result = queryResult.getresult()
        fields = queryResult.listfields()
        row = result[0]
        return unescape_bytea(row[fields.index('data')])
        
    def save_template_sound_data(self, template, data):
        sql = 'update template set data = \'%s\' where id = %s' % (escape_bytea(data), str(template.id))
        self.connection.query(sql)
    
    def insert_template(self, template):
        sql = 'insert into template (id, id_species, comment) values (default, %s, \'%s\') returning *' % (str(template.id_species), template.comment)
        queryResult = self.connection.query(sql)
        result = queryResult.getresult()
        fields = queryResult.listfields()
        row = result[0]
        template.id = row[fields.index('id')]
        return template
    
    def delete_template(self, template):
        self.connection.query('delete from template where id = %s' % template.id)
        
    def update_template(self, template):
        self.connection.query('update template set id_species = %s, comment = \'%s\' where id = %s' % (str(template.id_species), template.id, template.comment))
